-- Opdracht 1 | Products

/* Toon alle products waarbij de prod_name de tekst 'bean bag' bevat. */

--	prod_id		vend_id		prod_name				prod_price		prod_desc
--	BNBG01		DLL01 		Fish bean bag toy		3.49			Fish bean bag toy, complete with bean bag worms with which to feed it
--	BNBG02		DLL01 		Bird bean bag toy		3.49			Bird bean bag toy, eggs are not included
--	BNBG03		DLL01 		Rabbit bean bag toy		3.49			Rabbit bean bag toy, comes with bean bag carrots

SELECT *
FROM Products
WHERE prod_name LIKE '%bean bag%';

-- Opdracht 2 | OrderItems

/* Toon alle orderitems waarvan de prod_id begint met 4 willekeurige karakters, het getal 0 en 1 willekeurig karakter. */

--	order_num	order_item	prod_id	quantity	item_price
--	20007		2			BNBG01	100			2.99
--	20007		3			BNBG02	100			2.99
--	20007		4			BNBG03	100			2.99
--	20007		5			RGAN01	50			4.49
--	20008		1			RGAN01	5			4.99
--	20008		3			BNBG01	10			3.49
--	20008		4			BNBG02	10			3.49
--	20008		5			BNBG03	10			3.49
--	20009		1			BNBG01	250			2.49
--	20009		2			BNBG02	250			2.49
--	20009		3			BNBG03	250			2.49


SELECT * 
FROM OrderItems 
WHERE prod_id LIKE '____0_'; 

-- Opdracht 3 | Products

/* Toon alle products waarvan de prod_desc niet begint met een B of R. */

--	prod_id		vend_id		prod_name				prod_price		prod_desc
--	BNBG01		DLL01 		Fish bean bag toy		3.49			Fish bean bag toy, complete with bean bag worms with which to feed it
--	BR01		BRS01 		8 inch teddy bear		5.99			8 inch teddy bear, comes with cap and jacket
--	BR02		BRS01 		12 inch teddy bear 		8.99			12 inch teddy bear, comes with cap and jacket
--	BR03		BRS01 		18 inch teddy bear 		11.99			18 inch teddy bear, comes with cap and jacket
--	RGAN01		DLL01 		Raggedy Ann				4.99			18 inch	Raggedy Ann doll
--	RYL01 		FNG01 		King doll				9.49			12 inch	king doll with royal garments and crown
--	RYL02 		FNG01 		Queen doll 				9.49			12 inch queen doll with royal garments and crown

SELECT *
FROM Products
WHERE prod_desc LIKE '[^BR]%';

SELECT *
FROM Products
WHERE prod_desc NOT LIKE '[BR]%';

-- MySQL
SELECT *
FROM Products
WHERE prod_desc NOT REGEXP '^[BR]'

-- Opdracht 4 | Products

/* Breid opdracht 3 uit zodat de prod_id hoger dan een 1 eindigt. */

--	prod_id		vend_id		prod_name				prod_price		prod_desc
--	BR02		BRS01 		12 inch teddy bear 		8.99			12 inch teddy bear, comes with cap and jacket
--	BR03		BRS01 		18 inch teddy bear 		11.99			18 inch teddy bear, comes with cap and jacket
--	RYL02 		FNG01 		Queen doll 				9.49			12 inch queen doll with royal garments and crown

SELECT *
FROM Products
WHERE prod_desc NOT LIKE '[BR]%' AND RIGHT(RTRIM(prod_id),1) > 1;

-- MySQL
SELECT *
FROM Products
WHERE prod_desc NOT REGEXP '^[BR]' AND RIGHT(RTRIM(prod_id),1) > 1

-- Case sensitive: COLLATE SQL_Latin1_General_Cp1_CS_AS;